package sui.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;

import com.alibaba.fastjson.JSON;

import sui.utils.logs.LogUtils;

/**
 * <p>
 * Description: Excel工具类
 * <p>
 * @author XQF SUI
 * @Created: 2016年6月8日 下午6:09:55
 * @version 1.0
 */
public class ExcelUtils {
	private	Logger logger = LogUtils.get();
	// 格式化
	public final static DecimalFormat DF = new DecimalFormat("0");
	
	private File file;
	private Workbook workbook;
	private List<Sheet> sheets;
	private List<String> sheetsName;
	private int titleIndex;
	private int sheetIndex;
	private int sheetCount;
	private int rowIndex;
	private int colIndex;
	
	private List<List<String>> titles;
	private List<Map<String, Object>> appointSheetList;
	private List<List<Map<String, Object>>> sheetsList;
	
	/**
	 * 构造方法
	 * @param file			Excel文件
	 * @param sheetIndex	sheet页坐标
	 * @param titleIndex	标题横坐标
	 * @param rowIndex		数据横坐标
	 * @param colIndex		数据列坐标
	 */
	private ExcelUtils(File file, int sheetIndex, int titleIndex, int rowIndex, int colIndex) {
		this.file = file;
		this.sheetIndex = sheetIndex;
		this.titleIndex = titleIndex;
		this.rowIndex = rowIndex;
		this.colIndex = colIndex;
	}

	/**
	 * <p>
	 * Description：加载Excel文件
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:12:08
	 * @param file Excel文件
	 * @return ExcelUtils对象
	 * @throws ExcelException
	 */
	public static ExcelUtils loadExcelFile(File file) throws ExcelException {
		return loadExcelFile(file, 0, 0, 1, 0);
	}

	/**
	 * <p>
	 * Description：加载Excel文件
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:12:57
	 * @param file			Excel文件
	 * @param sheetIndex	sheet页坐标
	 * @param titleIndex	标题横坐标
	 * @param rowIndex		数据横坐标
	 * @param colIndex		数据列坐标
	 * @return ExcelUtils对象
	 * @throws ExcelException
	 */
	public static ExcelUtils loadExcelFile(File file, int sheetIndex, int titleIndex, int rowIndex, int colIndex)
			throws ExcelException {
		ExcelUtils excel = new ExcelUtils(file, sheetIndex, titleIndex, rowIndex, colIndex);
		
		if (!excel.initWorkbook())
			throw new ExcelException("初始化Workbook失败");
		if (!excel.initSheet())
			throw new ExcelException("初始化Sheet失败");
		if (!excel.initTitles())
			throw new ExcelException("初始化Titles失败");
		
		return excel;
	}
	
	/**
	 * <p>
	 * Description：
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:14:12
	 * @param file
	 * @param sheetIndex
	 * @param titleIndex
	 * @param rowIndex
	 * @param colIndex
	 * @return
	 * @throws ExcelException
	 */
	public static ExcelUtils loadExcelTemplate(File file, int sheetIndex, int titleIndex, int rowIndex, int colIndex)
			throws ExcelException {
		ExcelUtils excel = new ExcelUtils(file, sheetIndex, titleIndex, rowIndex, colIndex);
		
		if (!excel.initWorkbook())
			throw new ExcelException("初始化Workbook失败");
		if (!excel.initSheet())
			throw new ExcelException("初始化Sheet失败");
		if (!excel.initTitles())
			throw new ExcelException("初始化Titles失败");
		
		return excel;
	}

	/**
	 * <p>
	 * Description：将整个Excel文件内容转换为List<List<Map<String, Object>>>
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:14:23
	 * @return
	 */
	public List<List<Map<String, Object>>> excelToMap() {
		sheetsList = new ArrayList<List<Map<String, Object>>>();
		
		for (int i = 0; i < sheets.size(); i++) {
			sheetsList.add(excelToMap(i));
		}
		
		return sheetsList;
	}
	
	/**
	 * <p>
	 * Description：将Excel文件中指定sheet页转换为List<Map<String, Object>>
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:15:03
	 * @param sheetIndex
	 * @return
	 */
	public List<Map<String, Object>> excelToMap(int sheetIndex) {
		appointSheetList = new ArrayList<Map<String, Object>>();
		int j = 0;
		
		for (Row row : sheets.get(sheetIndex)) {
			Map<String, Object> rowMap = new LinkedHashMap<String, Object>();
			
			if (j++ < rowIndex) {
				continue;
			}
			
			for (int i = 0; i < titles.get(sheetIndex).size(); i++) {
				rowMap.put(titles.get(sheetIndex).get(i), getCellValues(row.getCell(i)));
			}
			
			appointSheetList.add(rowMap);
		}
		
		return appointSheetList;
	}
	
	/**
	 * <p>
	 * Description：将Excel中指定sheet页转成List<T>
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:15:43
	 * @param sheetIndex	sheet页坐标
	 * @param clazz			转换对象类型
	 * @return
	 */
	public <T> List<T> excelToList(int sheetIndex, Class<?> clazz) {
		return excelToList(sheetIndex, clazz, false, null);
	}
	
	/**
	 * <p>
	 * Description：将Excel中指定sheet页转成List<T>
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:17:13
	 * @param sheetIndex sheet页坐标
	 * @param clazz 转换对象类型
	 * @param isEnabledColumnAlias 是否启用列别名
	 * @param columnAliasMap 列别名Map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> excelToList(int sheetIndex, Class<?> clazz, boolean isEnabledColumnAlias,
			Map<String, String> columnAliasMap) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		int currentRowIndex = 0;
		
		for (Row row : sheets.get(sheetIndex)) {
			Map<String, Object> map = new HashMap<String, Object>();
			int i = 0;
			
			if (currentRowIndex < rowIndex) {
				currentRowIndex++;
				continue;
			}
			
			for (Cell cell : row) {
				if (isEnabledColumnAlias) {
					map.put(columnAliasMap.get(titles.get(sheetIndex).get(i++)), getCellValues(cell));
				} else {
					map.put(titles.get(sheetIndex).get(i++), getCellValues(cell));
				}
			}
			
			list.add(map);
		}
		
		return (List<T>) JSON.parseArray(JSON.toJSONString(list), clazz);
	}
	
	/**
	 * <p>
	 * Description：将List<T>生成Excel文件
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:18:19
	 * @param outFilePath	输出文件夹
	 * @param outFileName	输出文件名称
	 * @param list			List<T>
	 * @param sheetName		sheet页名称
	 * @param sheetIndex	sheet页坐标
	 * @param isEnabledColumnAlias	是否启用列别名
	 * @param columnAliasMap	列别名
	 * @return
	 * @throws IOException
	 * @throws ExcelException
	 * @throws InvalidFormatException
	 */
	public <T> boolean ListToExcel(String outFilePath, String outFileName, List<T> list, String sheetName,
			int sheetIndex, boolean isEnabledColumnAlias, Map<String, String> columnAliasMap)
			throws IOException, ExcelException, InvalidFormatException {
		
		String outFileStr = this.outFileCreate(outFilePath, outFileName, 0);
		
		FileInputStream fis = new FileInputStream(outFileStr);
		
		@SuppressWarnings("resource")
		Workbook workbook = new XSSFWorkbook(fis);
		Sheet sheet = workbook.createSheet(sheetName);
		
		for (int i = rowIndex; i < list.size() + rowIndex; i++) {
			Row row = sheet.getRow(i);
			
			if (row == null) {
				 row = sheet.createRow(i);
			}
			
			for (int j = colIndex; j < titles.get(this.sheetIndex).size(); j++) {
				Cell cell = row.getCell(j);
				
				if (cell == null) {
					cell = row.createCell(j);
				}
				
				cell.setCellValue((String) getBeanValues(list.get(i - rowIndex), columnAliasMap, j - colIndex));
			} 
		}
		
		try {
			FileOutputStream outputStream = new FileOutputStream(outFileStr);
			workbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			logger.error("It cause Error on WRITTING excel workbook: ", e);
			throw new ExcelException(e);
		}
		
		return true;
	}

	/**
	 * <p>
	 * Description：获取Bean对象的属性值
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:20:09
	 * @param t	Bean对象
	 * @param columnAliasMap 列别名
	 * @param colIndex 列坐标
	 * @return
	 */
	private <T>  Object getBeanValues(T t, Map<String, String> columnAliasMap, int colIndex) {
		return JSON.parseObject(JSON.toJSONString(t), Map.class).get(columnAliasMap.get(titles.get(sheetIndex).get(colIndex)));
	}

	/**
	 * <p>
	 * Description：创建输出文件
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:21:20
	 * @param outFilePath
	 * @param outFileName
	 * @param index
	 * @return
	 * @throws IOException
	 * @throws ExcelException
	 */
	private String outFileCreate(String outFilePath, String outFileName, int index) throws IOException, ExcelException {
		File outFileFolder = new File(outFilePath);
		
		if (!outFileFolder.exists()) {
			outFileFolder.mkdirs();
		}
		
		File outFile = new File(outFilePath, outFileName);
		
		if (outFile.exists()) {
			// 如果文件名称重复采取 "fileName-副本(i).*" 的命名方式
			StringBuilder builder = new StringBuilder(outFile.getName().substring(0, file.getName().lastIndexOf(".")));
			builder.append("-副本(").append(index).append(")");
			builder.append(outFile.getName().substring(file.getName().lastIndexOf(".")));
			
			outFile = new File(outFilePath, builder.toString());
			
			if (outFile.exists()) {
				return outFileCreate(outFilePath, outFileName, ++index);
			} else {
				if (outFile.createNewFile()) {
					FileUtils.copyFile(file, outFile);
					return outFile.getCanonicalPath();
				} else {
					throw new ExcelException("创建Excel失败");
				}
			}
		} else {
			if (outFile.createNewFile()) {
				return outFile.getCanonicalPath();
			} else {
				throw new ExcelException("创建Excel失败");
			}
		}
	}

	/**
	 * <p>
	 * Description：初始化标题
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:22:55
	 * @return
	 */
	private boolean initTitles() {
		try {
			logger.info("初始化表头...");
			
			titles = new ArrayList<List<String>>();
			
			for (Sheet sheet : sheets) {
				List<String> list = new ArrayList<String>();
				Row rows = sheet.getRow(titleIndex);
				
				for (Cell cell : rows) {
					list.add(getCellValues(cell).toString());
				}
				
				titles.add(list);
			}
		} catch (Exception e) {
			logger.error("初始化表头错误", e);
			return false;
		}
		
		return true;
	}

	/**
	 * <p>
	 * Description：初始化sheet页
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:23:12
	 * @return
	 */
	private boolean initSheet() {
		try {
			logger.info("初始化Sheet...");
			
			sheets = new ArrayList<Sheet>();
			sheetsName = new ArrayList<String>();
			
			for (int i = 0; i < sheetCount; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				String sheetName = workbook.getSheetName(i);
				
				sheets.add(sheet);
				sheetsName.add(sheetName);
			}
		} catch (Exception e) {
			logger.error("初始化sheet失败", e);
			return false;
		}
		
		return true;
	}

	/**
	 * <p>
	 * Description：初始化工作薄
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:23:29
	 * @return
	 */
	private boolean initWorkbook() {
		try {
			logger.info("初始化Workbook...");
			workbook = WorkbookFactory.create(file);
			sheetCount = workbook.getNumberOfSheets();
		} catch (Exception e) {
			logger.error("初始化Workbook失败", e);
			return false;
		}
		
		return true;
	}
	
	/**
	 * <p>
	 * Description：获取Cell值(简略版, 在此可根据自己需要, 自行可改进)
	 * </p>
	 * @author XQF SUI
	 * @created 2016年6月8日 下午6:23:52
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static Object getCellValues(Cell cell) {
		return cell.getCellType() == 1 ? cell.getStringCellValue().trim() : DF.format(cell.getNumericCellValue());
	}
	
	public static void main(String[] args) throws ExcelException {
		ExcelUtils excelFile = ExcelUtils.loadExcelFile(new File("F:\\window\\Desktop\\8.xlsx"));
		
		System.out.println(JSON.toJSONString(excelFile.excelToMap(0)));
		System.out.println(JSON.toJSONString(excelFile.excelToMap()));
	}
}
